CREATE OR REPLACE PACKAGE YieldHandler IS

--===============================================================
-- Package information
--===============================================================
FUNCTION Version RETURN VARCHAR2
;
--===============================================================
-- Type declarations
--===============================================================
TYPE TYPE_YieldHandler_InputData IS RECORD
(
    ContainerName Container.ContainerName%TYPE
    , LotType Container.Lottype%TYPE
    , MainQty NUMBER
    , SpecName SpecBase.SpecName%TYPE
    , QtyToProcess NUMBER
    , ProcessSequence A_WIPLotDetails.ProcessSequence%TYPE
    , OperationGroupIsChanged VARCHAR2(1)
    , LossReasonNames TRGUtil.COLLECTION
    , LossReasonQtys TRGUtil.COLLECTION
    , RejectQtys TRGUtil.COLLECTION
    , DefectQtys TRGUtil.COLLECTION
    , BonusBackRejectQtys TRGUtil.COLLECTION
    , Bins TRGUtil.COLLECTION
    , BinsQtys TRGUtil.COLLECTION
    , WIPDataServiceNames TRGUtil.COLLECTION
    , WIPDataNames TRGUtil.COLLECTION
    , WIPDataValues TRGUtil.COLLECTION
)
;
TYPE TYPE_BinsLimits IS RECORD
(
    LimitType A_BinsLimits.LimitType%TYPE
    , LimitCondition A_BinsLimits.LimitCondition%TYPE
    , LimitInPercentage A_BinsLimits.LimitInPercentage%TYPE
    , LimitInUnits A_BinsLimits.LimitInUnits%TYPE
    , Bins TRGUtil.COLLECTION
)
;
TYPE COLL_BinsLimits IS TABLE OF TYPE_BinsLimits INDEX BY BINARY_INTEGER
;
TYPE TYPE_LossReasonLimits IS RECORD
(
    LossReasonId A_LossReasonLimits.LossReasonId%TYPE
    , LossReasonName LossReason.LossReasonName%TYPE
    , LimitCondition A_LossReasonLimits.LimitCondition%TYPE
    , LimitInPercentage A_LossReasonLimits.LimitInPercentage%TYPE
    , LimitInUnits A_LossReasonLimits.LimitInUnits%TYPE
    , LossReasonNames TRGUtil.COLLECTION
)
;
TYPE COLL_LossReasonLimits IS TABLE OF TYPE_LossReasonLimits INDEX BY BINARY_INTEGER
;
TYPE TYPE_LotSizeLimits IS RECORD
(
    LotSize NUMBER := 0
    , LowerLimit NUMBER := 0
    , YieldCutLimit NUMBER := 0
)
;
TYPE TYPE_YieldLimits IS RECORD
(
    CriteriaId CHAR(16)
    , UpperYieldLimit NUMBER := 0
    , UpperYieldFailureAction VARCHAR2(40) := 'HOLD'
    , UpperYieldEmailGroupId CHAR(16)
    , LowerYieldLimit NUMBER := 0
    , LowerYieldFailureAction VARCHAR2(40) := 'HOLD'
    , LowerYieldEmailGroupId CHAR(16)
    , YieldCutYieldLimit NUMBER := 0
    , YieldCutYieldEmailGroupId CHAR(16)
    , BinsLimitsRequired VARCHAR2(1) := 0
    , BinsLimitsFailureAction VARCHAR2(40) := 'HOLD'
    , BinsLimitsEmailGroupId CHAR(16)
    , LossReasonLimitsRequired VARCHAR2(1) := 0
    , LossReasonLimitsFailureAction VARCHAR2(40) := 'HOLD'
    , LossReasonLimitsEmailGroupId CHAR(16)
    , LotSizeLimitsRequired VARCHAR2(1) := 0
    , LotSizeLimitsFailureAction VARCHAR2(40) := 'HOLD'
    , LotSizeLowerLimitEmailGroupId CHAR(16)
    , LotSizeYieldCutEmailGroupId CHAR(16)
)
;
TYPE TYPE_PDALimits IS RECORD
(
    CriteriaId A_PDALimits.PDALimitsId%TYPE
    , LossReasonsSpecId A_PDALimits.LossReasonsSpecId%TYPE
    , ReferenceSpecId A_PDALimits.ReferenceSpecId%TYPE
)
;
TYPE TYPE_YieldHandler_ServiceData IS RECORD
(
    --=====================
    -- Lot data
    --=====================
    AssemblyPlant A_LotAttributes.DateCode%TYPE
    , BucketNumber A_LotAttributes.DateCode%TYPE
    , ContainerId Container.ContainerId%TYPE
    , LotType Container.Lottype%TYPE
    , FabLotNumber A_LotAttributes.DateCode%TYPE
    , InsertionNumber Container.InsertionNumber%TYPE
    , LotSizeLimitsRequired Spec.LotSizeLimitsRequired%TYPE
    , MoveInQty Container.MoveInQty%TYPE
    , OriginalQty Container.OriginalQty%TYPE
    , OperationGroupId Operation.OperationGroupId%TYPE
    , OPN A_LotAttributes.DateCode%TYPE
    , OwnerId Container.OwnerId%TYPE
    , PDALimitsRequired Spec.PDALimitsRequired%TYPE
    , ProcessSpecId Container.ProcessSpecId%TYPE
    , ProductId Container.ProductId%TYPE
    , ScheduleDataId Container.ScheduleDataId%TYPE
    , SpecId Spec.SpecId%TYPE
    , StepLogicId Spec.StepLogicId%TYPE
    , StepLogicCriteriaId Container.StepLogicCriteriaId%TYPE
    , YieldFailureEmailGroupId Spec.YieldFailureEmailGroupId%TYPE
    , YieldTypeId Spec.YieldTypeId%TYPE
    , YieldTypeName A_YieldType.YieldTypeName%TYPE
    , WIPLotId A_WIPLotDetails.WIPLotId%TYPE
    --=====================
    -- Process Spec data
    --=====================
    , ProcessSpecObjectCategory A_ProcessSpec.ObjectCategory%TYPE
    , ProcessSpecObjectType A_ProcessSpec.ObjectType%TYPE
    --=====================
    -- Product data
    --=====================
    , Device Product.Device%TYPE
    , DeviceVersion Product.DeviceVersion%TYPE
    , PackageType Product.PackageType%TYPE
    , Leads Product.Leads%TYPE
    , ProductLineId Product.ProductLineId%TYPE
    --=====================
    -- Other product data
    --=====================
    , OperatingRange A_ProcessSpecParams.ParamValue%TYPE
    , StepLogicName A_StepLogic.StepLogicName%TYPE
    --=====================
    -- WIP Lot Details data
    --=====================
    , HoldLotIfLimitsNotFound A_WIPLot.HoldLotIfLimitsNotFound%TYPE
    , ProcessTypeId A_ProcessType.ProcessTypeId%TYPE
    , ProcessTypeName A_ProcessType.ProcessTypeName%TYPE
    , StepCount A_WIPLot.StepCount%TYPE
    , TestProgramName A_WIPLotDetails.TestProgramName%TYPE
    , TestStatus A_WIPLotDetails.TestStatus%TYPE
    , WIPTestStatus A_WIPLotDetails.WIPTestStatus%TYPE
    , TestPlan A_WIPLotDetails.TestPlan%TYPE
    , TestSubPlan A_WIPLotDetails.TestSubPlan%TYPE
    , WIPLotDetailsId A_WIPLotDetails.WIPLotDetailsId%TYPE
    --=====================
    -- Limits data
    --=====================
    , YieldLimits TYPE_YieldLimits
    , BinsLimits COLL_BinsLimits
    , LossReasonLimits COLL_LossReasonLimits
    , LotSizeLimits TYPE_LotSizeLimits
    , FailureCategory A_WIPLotFailures.FailureCategory%TYPE
    , YieldValue NUMBER := 0
    , RejectYieldValue NUMBER := 0
    , ProcessYieldResult VARCHAR2(40)
    , LimitsFound NUMBER := 0
    , PDALimits TYPE_PDALimits
    --=====================
    -- Limits result
    --=====================
    , FailYieldCut NUMBER := 0
    , FailCommonLImits NUMBER := 0
    , FailBinsLImits NUMBER := 0
    , FailLossReasonLImits NUMBER := 0
    , FailLotSizeLImits NUMBER := 0
    , FailPDALimits NUMBER := 0
    , FailStepLogic NUMBER := 0
    --=====================
    -- Other data
    --=====================
    , YieldQty NUMBER := 0
    , TotalRejectQty NUMBER := 0
    , TotalDefectQty NUMBER := 0
    , TotalBonusBackRejectQty NUMBER := 0
    , TotalRejectBisQty NUMBER := 0
    , TotalBadQty NUMBER := 0
    , WIPLotFailuresCDOTypeId NUMBER := 0
)
;

--===============================================================
-- Function and Procedure declarations
--===============================================================
FUNCTION ExecuteData (ContainerName IN VARCHAR2
                      , MainQty IN NUMBER
                      , SpecName IN VARCHAR2
                      , QtyToProcess IN NUMBER
                      , ProcessSequence IN NUMBER
                      , OperationGroupIsChanged IN VARCHAR2
                      , LossReasonNames IN VARCHAR2
                      , RejectQtys IN VARCHAR2
                      , DefectQtys IN VARCHAR2
                      , BonusBackRejectQtys IN VARCHAR2
                      , Bins IN VARCHAR2
                      , BinsQtys IN VARCHAR2
                      , WIPDataServiceNames IN VARCHAR2 DEFAULT ''
                      , WIPDataNames IN VARCHAR2 DEFAULT ''
                      , WIPDataValues IN VARCHAR2 DEFAULT '')
RETURN VARCHAR2
;
PROCEDURE GetOtherLimits (InputData IN TYPE_YieldHandler_InputData
                          , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
PROCEDURE CheckLimits (InputData IN TYPE_YieldHandler_InputData
                       , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
PROCEDURE CheckCommonLimits (InputData IN TYPE_YieldHandler_InputData
                             , ServiceData IN OUT TYPE_YieldHandler_ServiceData
                             , CheckUpperYieldLimit NUMBER DEFAULT 1
                             , CheckLowerYieldLimit NUMBER DEFAULT 1
                             , CheckYieldCutYieldLimit NUMBER DEFAULT 1)
;
PROCEDURE CheckLotSizeLimits (InputData IN TYPE_YieldHandler_InputData
                              , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
PROCEDURE CheckBinsLimits (InputData IN TYPE_YieldHandler_InputData
                           , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
PROCEDURE CheckLossReasonLimits (InputData IN TYPE_YieldHandler_InputData
                                 , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
PROCEDURE InsertFailure(InputData IN TYPE_YieldHandler_InputData
                        , ServiceData IN OUT TYPE_YieldHandler_ServiceData
                        , FailureType IN VARCHAR2
                        , FailureCondition IN VARCHAR2
                        , YieldValue NUMBER
                        , YieldLimit NUMBER
                        , FailureAction IN VARCHAR2
                        , EmailGroupId IN VARCHAR2
                        , Bins IN VARCHAR2 DEFAULT ''
                        , LossReasonName IN VARCHAR2 DEFAULT ''
                        , LossReasonNames IN VARCHAR2 DEFAULT '')
;
PROCEDURE ExecuteASSEMBLY (InputData IN TYPE_YieldHandler_InputData
                           , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
PROCEDURE ExecuteBURNIN (InputData IN TYPE_YieldHandler_InputData
                         , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
PROCEDURE ExecuteTMP (InputData IN TYPE_YieldHandler_InputData
                      , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
PROCEDURE ExecuteSORTING (InputData IN TYPE_YieldHandler_InputData
                          , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
PROCEDURE ExecutePDA (InputData IN TYPE_YieldHandler_InputData
                      , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
;
--===========================================================================
-- End of package specification
--===========================================================================
END;
/
CREATE OR REPLACE PACKAGE BODY YIELDHANDLER AS

--=====================
-- Package information
--=====================
FUNCTION Version RETURN VARCHAR2 IS BEGIN RETURN ('3.4.0001'); END;

--=====================
-- ExecuteData
--=====================
FUNCTION ExecuteData (ContainerName IN VARCHAR2
                      , MainQty IN NUMBER
                      , SpecName IN VARCHAR2
                      , QtyToProcess IN NUMBER
                      , ProcessSequence IN NUMBER
                      , OperationGroupIsChanged IN VARCHAR2
                      , LossReasonNames IN VARCHAR2
                      , RejectQtys IN VARCHAR2
                      , DefectQtys IN VARCHAR2
                      , BonusBackRejectQtys IN VARCHAR2
                      , Bins IN VARCHAR2
                      , BinsQtys IN VARCHAR2
                      , WIPDataServiceNames IN VARCHAR2 DEFAULT ''
                      , WIPDataNames IN VARCHAR2 DEFAULT ''
                      , WIPDataValues IN VARCHAR2 DEFAULT '')
RETURN VARCHAR2 IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curLotInfo (IN_ContainerName Container.ContainerName%TYPE) IS
    SELECT
        SUBSTR(B.DateCode, 6, 1) AssemblyPlant
        , B.BucketNumber BucketNumber
        , A.ContainerId ContainerId
        , B.FabLotNumber FabLotNumber
        , A.InsertionNumber InsertionNumber
        , DECODE(A.MoveInQty, 0, A.MoveInQty2, A.MoveInQty) MoveInQty
        , A.OriginalQty OriginalQty
        , B.OPN OPN
        , A.OwnerId OwnerId
        , A.ProcessSpecId ProcessSpecId
        , A.ProductId ProductId
        , A.ScheduleDataId ScheduleDataId
        , A.StepLogicCriteriaId StepLogicCriteriaId
        , A.CurrentWIPLotId WIPLotId
        , A.Lottype LotType
    FROM
        Container A
        , A_LotAttributes B
    WHERE
        A.ContainerId = B.ContainerId
        AND A.LOTTYPE IN ('M', 'F', 'E','Q','A','R','P','W','L')
        AND A.ContainerName = IN_ContainerName
    ;
    CURSOR curStepInfo (IN_SpecName SpecBase.SpecName%TYPE) IS
    SELECT
        B.SpecId SpecId
        , B.LotSizeLimitsRequired LotSizeLimitsRequired
        , C.OperationGroupId OperationGroupId
        , B.PDALimitsRequired PDALimitsRequired
        , B.YieldFailureEmailGroupId YieldFailureEmailGroupId
        , B.YieldTypeId YieldTypeId
        , D.YieldTypeName YieldTypeName
        , B.StepLogicId StepLogicId
        , E.StepLogicName StepLogicName
    FROM
        SpecBase A
        , Spec B
        , Operation C
        , A_YieldType D
        , A_StepLogic E
    WHERE
        A.SpecName = IN_SpecName
        AND A.SpecBaseId = B.SpecBaseId
        AND B.OperationId = C.OperationId
        AND B.YieldTypeId = D.YieldTypeId (+)
        AND B.StepLogicId = E.StepLogicId (+)
    ;
    CURSOR curProcessSpecInfo (IN_ProcessSpecId A_ProcessSpec.ProcessSpecId%TYPE) IS
    SELECT
        ObjectCategory
        , ObjectType
    FROM
        A_ProcessSpec
    WHERE
        ProcessSpecId = IN_ProcessSpecId
    ;
    CURSOR curProductInfo (IN_ProductId Product.ProductId%TYPE) IS
    SELECT
        --==============
        -- Get Device information from QADDevice column
        -- Deng Chao 2009-04-20
        --==============
        QADDevice Device
        --Device
        , DeviceVersion
        , PackageType
        , Leads
        , ProductLineId
    FROM
        Product
    WHERE
        ProductId = IN_ProductId
    ;
    CURSOR curOperatingRange (IN_ProcessSpecId A_ProcessSpec.ProcessSpecId%TYPE) IS
    SELECT
        ParamValue OperatingRange
    FROM
        A_ProcessSpecParams
    WHERE
        ProcessSpecId = IN_ProcessSpecId
        AND UPPER(ParamName) = 'OPERATINGRANGE'
    ;
    CURSOR curWIPLotDetailsInfo (IN_WIPLotId A_WIPLotDetails.WIPLotId%TYPE,
                                 IN_ProcessSequence A_ProcessType.ProcessTypeName%TYPE) IS
    SELECT
        A.HoldLotIfLimitsNotFound
        , B.ProcessTypeId
        , C.ProcessTypeName
        , A.StepCount
        , B.TestProgramName
        , B.TestStatus
        , B.WIPTestStatus
        , B.TestPlan
        , B.TestSubPlan
        , B.WIPLotDetailsId
        , B.ProcessYieldResult
    FROM
        A_WIPLot A
        , A_WIPLotDetails B
        , A_ProcessType C
    WHERE
        A.WIPLotId = B.WIPLotId
        AND B.ProcessTypeId = C.ProcessTypeId
        AND A.WIPLotId = IN_WIPLotId
        AND B.ProcessSequence = IN_ProcessSequence
    ;
    --=====================
    -- Local variables
    --=====================
    rLotInfo curLotInfo%ROWTYPE;
    rStepInfo curStepInfo%ROWTYPE;
    rProcessSpecInfo curProcessSpecInfo%ROWTYPE;
    rProductInfo curProductInfo%ROWTYPE;
    rOperatingRange curOperatingRange%ROWTYPE;
    rWIPLotDetailsInfo curWIPLotDetailsInfo%ROWTYPE;
    tInputData TYPE_YieldHandler_InputData;
    tServiceData TYPE_YieldHandler_ServiceData;
    nCount NUMBER;
    nIsStepLogic NUMBER := 0;
    --=====================
    -- Pragma Declarations
    --=====================
    PRAGMA AUTONOMOUS_TRANSACTION;
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Save the input parameters
    --=====================
    tInputData.ContainerName := ContainerName;
    tInputData.MainQty := MainQty;
    tInputData.SpecName := SpecName;
    tInputData.QtyToProcess := QtyToProcess;
    tInputData.ProcessSequence := ProcessSequence;
    tInputData.OperationGroupIsChanged := OperationGroupIsChanged;
    tInputData.LossReasonNames := TRGUtil.SetCollection(TRIM(LossReasonNames), ',');
    tInputData.RejectQtys := TRGUtil.SetCollection(TRIM(RejectQtys), ',');
    tInputData.DefectQtys := TRGUtil.SetCollection(TRIM(DefectQtys), ',');
    tInputData.BonusBackRejectQtys := TRGUtil.SetCollection(TRIM(BonusBackRejectQtys), ',');
    tInputData.Bins := TRGUtil.SetCollection(TRIM(Bins), ',');
    tInputData.BinsQtys := TRGUtil.SetCollection(TRIM(BinsQtys), ',');
    tInputData.WIPDataServiceNames := TRGUtil.SetCollection(TRIM(WIPDataServiceNames), ',');
    tInputData.WIPDataNames := TRGUtil.SetCollection(TRIM(WIPDataNames), ',');
    tInputData.WIPDataValues := TRGUtil.SetCollection(TRIM(WIPDataValues), ',');
    --=====================
    -- Fetch Lot info
    --=====================
    OPEN curLotInfo(tInputData.ContainerName);
    FETCH curLotInfo INTO rLotInfo;
    IF curLotInfo%FOUND THEN
        tServiceData.ContainerId := rLotInfo.ContainerId;
        tServiceData.LotType := rLotInfo.LotType;
        tServiceData.AssemblyPlant := rLotInfo.AssemblyPlant;
        tServiceData.BucketNumber := rLotInfo.BucketNumber;
        tServiceData.FabLotNumber := rLotInfo.FabLotNumber;
        tServiceData.InsertionNumber := rLotInfo.InsertionNumber;
        tServiceData.MoveInQty := rLotInfo.MoveInQty;
        tServiceData.OriginalQty := rLotInfo.OriginalQty;
        tServiceData.OPN := rLotInfo.OPN;
        tServiceData.OwnerId := rLotInfo.OwnerId;
        tServiceData.ProcessSpecId := rLotInfo.ProcessSpecId;
        tServiceData.ProductId := rLotInfo.ProductId;
        tServiceData.ScheduleDataId := rLotInfo.ScheduleDataId;
        tServiceData.StepLogicCriteriaId := rLotInfo.StepLogicCriteriaId;
        tServiceData.WIPLotId := rLotInfo.WIPLotId;
    END IF;
    CLOSE curLotInfo;
    --=====================
    -- Fetch Step Info
    --=====================
    OPEN curStepInfo(tInputData.SpecName);
    FETCH curStepInfo INTO rStepInfo;
    IF curStepInfo%FOUND THEN
        tServiceData.SpecId := rStepInfo.SpecId;
        tServiceData.LotSizeLimitsRequired := rStepInfo.LotSizeLimitsRequired;
        tServiceData.OperationGroupId := rStepInfo.OperationGroupId;
        tServiceData.PDALimitsRequired := rStepInfo.PDALimitsRequired;
        tServiceData.YieldFailureEmailGroupId := rStepInfo.YieldFailureEmailGroupId;
        tServiceData.YieldTypeId := rStepInfo.YieldTypeId;
        tServiceData.YieldTypeName := UPPER(rStepInfo.YieldTypeName);
        tServiceData.StepLogicId := rStepInfo.StepLogicId;
        tServiceData.StepLogicName := UPPER(rStepInfo.StepLogicName);
    END IF;
    CLOSE curStepInfo;
    --=====================
    -- Fetch Process Spec info
    --=====================
    OPEN curProcessSpecInfo(tServiceData.ProcessSpecId);
    FETCH curProcessSpecInfo INTO rProcessSpecInfo;
    IF curProcessSpecInfo%FOUND THEN
        tServiceData.ProcessSpecObjectCategory := rProcessSpecInfo.ObjectCategory;
        tServiceData.ProcessSpecObjectType := rProcessSpecInfo.ObjectType;
    END IF;
    CLOSE curProcessSpecInfo;
    --=====================
    -- Fetch Product info
    --=====================
    OPEN curProductInfo(tServiceData.ProductId);
    FETCH curProductInfo INTO rProductInfo;
    IF curProductInfo%FOUND THEN
        tServiceData.Device := rProductInfo.Device;
        tServiceData.DeviceVersion := rProductInfo.DeviceVersion;
        tServiceData.PackageType := rProductInfo.PackageType;
        tServiceData.Leads := rProductInfo.Leads;
        tServiceData.ProductLineId := rProductInfo.ProductLineId;
    END IF;
    CLOSE curProductInfo;
    --=====================
    -- Fetch the Operating Range
    --=====================
    OPEN curOperatingRange(tServiceData.ProcessSpecId);
    FETCH curOperatingRange INTO rOperatingRange;
    IF curOperatingRange%FOUND THEN
        tServiceData.OperatingRange := rOperatingRange.OperatingRange;
    END IF;
    CLOSE curOperatingRange;
    --=====================
    -- Fetch WIPLotDetails info
    --=====================
    OPEN curWIPLotDetailsInfo(tServiceData.WIPLotId, tInputData.ProcessSequence);
    FETCH curWIPLotDetailsInfo INTO rWIPLotDetailsInfo;
    IF NOT curWIPLotDetailsInfo%NOTFOUND THEN
        tServiceData.HoldLotIfLimitsNotFound := rWIPLotDetailsInfo.HoldLotIfLimitsNotFound;
        tServiceData.ProcessTypeId := rWIPLotDetailsInfo.ProcessTypeId;
        tServiceData.ProcessTypeName := rWIPLotDetailsInfo.ProcessTypeName;
        tServiceData.StepCount := rWIPLotDetailsInfo.StepCount;
        tServiceData.TestProgramName := rWIPLotDetailsInfo.TestProgramName;
        tServiceData.TestStatus := rWIPLotDetailsInfo.TestStatus;
        tServiceData.WIPTestStatus := rWIPLotDetailsInfo.WIPTestStatus;
        tServiceData.TestPlan := rWIPLotDetailsInfo.TestPlan;
        tServiceData.TestSubPlan := rWIPLotDetailsInfo.TestSubPlan;
        tServiceData.WIPLotDetailsId := rWIPLotDetailsInfo.WIPLotDetailsId;
        tServiceData.ProcessYieldResult := rWIPLotDetailsInfo.ProcessYieldResult;
    END IF;
    CLOSE curWIPLotDetailsInfo;
    --=====================
    -- Delete previous failures
    --=====================
    DELETE
        A_WIPLotFailuresTemp
    WHERE
        ContainerId = tServiceData.ContainerId
    ;
    --=====================
    -- Calculate the total reject quantity
    --=====================
    FOR nCount IN 0..tInputData.LossReasonNames.COUNT-1 LOOP
        tServiceData.TotalRejectQty := tServiceData.TotalRejectQty + tInputData.RejectQtys(nCount);
        tServiceData.TotalDefectQty := tServiceData.TotalDefectQty + tInputData.DefectQtys(nCount);
        tServiceData.TotalBonusBackRejectQty := tServiceData.TotalBonusBackRejectQty + tInputData.BonusBackRejectQtys(nCount);
        tInputData.LossReasonQtys(nCount) := tInputData.RejectQtys(nCount) + tInputData.DefectQtys(nCount) - tInputData.BonusBackRejectQtys(nCount);
    END LOOP;
--    FOR nCount IN 0..tInputData.RejectQtys.COUNT-1 LOOP
--        tServiceData.TotalRejectQty := tServiceData.TotalRejectQty + tInputData.RejectQtys(nCount);
--    END LOOP;
--    FOR nCount IN 0..tInputData.DefectQtys.COUNT-1 LOOP
--        tServiceData.TotalDefectQty := tServiceData.TotalDefectQty + tInputData.DefectQtys(nCount);
--    END LOOP;
--    FOR nCount IN 0..tInputData.BonusBackRejectQtys.COUNT-1 LOOP
--        tServiceData.TotalBonusBackRejectQty := tServiceData.TotalBonusBackRejectQty + tInputData.BonusBackRejectQtys(nCount);
--    END LOOP;
    FOR nCount IN 0..tInputData.BinsQtys.COUNT-1 LOOP
        IF tInputData.BinsQtys(nCount) < 0 THEN
            tServiceData.TotalRejectBisQty := tServiceData.TotalRejectBisQty + ABS(tInputData.BinsQtys(nCount));
        END IF;
    END LOOP;
    tServiceData.TotalBadQty := tServiceData.TotalRejectQty + tServiceData.TotalDefectQty - tServiceData.TotalBonusBackRejectQty + tServiceData.TotalRejectBisQty;
    --=====================
    -- Set the Yield Data
    --=====================
    IF tServiceData.InsertionNumber = 1 THEN
        IF tInputData.QtyToProcess > 0 THEN
            tServiceData.YieldQty := tInputData.QtyToProcess;
        ELSE
            tServiceData.YieldQty := tInputData.MainQty;
        END IF;
    ELSE
        tServiceData.YieldQty := tInputData.MainQty;
    END IF;
    --=====================
    -- Ensure a Yield Qty exists
    --=====================
    IF tServiceData.YieldQty > 0 THEN
        tServiceData.YieldValue := 100 * (tServiceData.YieldQty - tServiceData.TotalBadQty) / tServiceData.YieldQty;
        tServiceData.RejectYieldValue := 100 * tServiceData.TotalBadQty / tServiceData.YieldQty;
        --=====================
        -- Select the Yield Type Name
        --=====================
        IF tServiceData.YieldTypeName = 'ASSEMBLY' THEN
            ExecuteASSEMBLY (tInputData, tServiceData);
        ELSIF tServiceData.YieldTypeName = 'BURNIN' THEN
            ExecuteBURNIN (tInputData, tServiceData);
        ELSIF tServiceData.YieldTypeName = 'TMP' THEN
            ExecuteTMP (tInputData, tServiceData);
        ELSIF (tServiceData.YieldTypeName = 'WAFERSORT') OR (tServiceData.YieldTypeName = 'SORTING') THEN
            ExecuteSORTING (tInputData, tServiceData);
        ELSIF tServiceData.YieldTypeName IS NOT NULL THEN
            RAISE_APPLICATION_ERROR (-20005, 'YieldHandler_E1001 ' || tServiceData.YieldTypeName);
        END IF;
        --=====================
        -- Check whether PDA limits is required
        --=====================
        IF tServiceData.PDALimitsRequired = 1 THEN
            ExecutePDA (tInputData, tServiceData);
        END IF;
    END IF;
    --=====================
    -- Commit the data
    --=====================
    COMMIT;
    --=====================
    -- Return success and the Process Yield Result
    --=====================
    IF tServiceData.FailYieldCut = 0 AND
       tServiceData.FailCommonLimits < 1 AND
       tServiceData.FailBinsLimits = 0 AND
       tServiceData.FailLossReasonLimits = 0 AND
       tServiceData.FailLotSizeLimits < 1 AND
       tServiceData.FailPDALimits = 0 AND
       tServiceData.FailStepLogic = 0 THEN
        IF tServiceData.FailCommonLimits = -1 OR tServiceData.FailLotSizeLimits = -1 THEN
            tServiceData.ProcessYieldResult := 'WAIVE';
        ELSIF tServiceData.LimitsFound = 1 THEN
            tServiceData.ProcessYieldResult := 'PASS';
        ELSIF nIsStepLogic = 0 THEN
            IF tServiceData.HoldLotIfLimitsNotFound = 1 THEN
                tServiceData.FailureCategory := 'NOSETUP';
                InsertFailure (tInputData
                               , tServiceData
                               , 'NOSETUP'
                               , '-'
                               , -1
                               , -1
                               , 'HOLD'
                               , tServiceData.YieldFailureEmailGroupId
                              );
                tServiceData.ProcessYieldResult := 'FAIL';
            ELSE
                tServiceData.ProcessYieldResult := 'PASS';
            END IF;
        ELSE
            tServiceData.ProcessYieldResult := 'PASS';
        END IF;
    ELSE
        tServiceData.ProcessYieldResult := 'FAIL';
    END IF;
    RETURN ('0 ' || tServiceData.ProcessYieldResult);
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RETURN ('YieldHandler.ExecuteData->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RETURN (REPLACE(SQLERRM, 'ORA-20005: '));
    WHEN OTHERS THEN
        ROLLBACK;
        RETURN ('YieldHandler.ExecuteData->' || SQLERRM);
END ExecuteData;

--=====================
-- GetOtherLimits
--=====================
PROCEDURE GetOtherLimits (InputData IN TYPE_YieldHandler_InputData
                          , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curBinsLimits IS
    SELECT
        BinsLimitsId
        , LimitType
        , LimitCondition
        , LimitInPercentage
        , LimitInUnits
    FROM
        A_BinsLimits
    WHERE
        YieldLimitsId = ServiceData.YieldLimits.CriteriaId
    ORDER BY
        LimitSequence
    ;
    CURSOR curBinsLimitsDetails (IN_BinsLimitsId A_BinsLimitsDetails.BinsLimitsId%TYPE) IS
    SELECT
        Bin
    FROM
        A_BinsLimitsDetails
    WHERE
        BinsLimitsId = IN_BinsLimitsId
    ORDER BY
        Bin
    ;
    CURSOR curLossReasonLimits IS
    SELECT
        A.LossReasonLimitsId
        , A.LossReasonId LossReasonId
        , B.LossReasonName LossReasonName
        , A.LimitCondition LimitCondition
        , A.LimitInPercentage LimitInPercentage
        , A.LimitInUnits LimitInUnits
    FROM
        A_LossReasonLimits A
        , LossReason B
    WHERE
        A.YieldLimitsId = ServiceData.YieldLimits.CriteriaId
        AND A.LossReasonId = B.LossReasonId (+)
    ORDER BY
        A.LimitSequence
    ;
    CURSOR curLossReasonLimitsDetails (IN_LossReasonLimitsId A_LossReasonLimitsDetails.LossReasonLimitsId%TYPE) IS
    SELECT
        B.LossReasonName
    FROM
        A_LossReasonLimitsDetails A
        , LossReason B
    WHERE
        A.LossReasonId = B.LossReasonId
        AND A.LossReasonLimitsId = IN_LossReasonLimitsId
    ORDER BY
        B.LossReasonName
    ;
    CURSOR curLotSizeLimits IS
    SELECT
        LotSize
        , LowerLimit
        , YieldCutLimit
    FROM
        A_LotSizeLimits
    WHERE
        YieldLimitsId = ServiceData.YieldLimits.CriteriaId
        AND LotSize >= InputData.MainQty
    ORDER BY
        LotSize DESC
    ;
    --=====================
    -- Local variables
    --=====================
    rBinsLimits curBinsLimits%ROWTYPE;
    rBinsLimitsDetails curBinsLimitsDetails%ROWTYPE;
    tBinsLimits TYPE_BinsLimits;
    rLossReasonLimits curLossReasonLimits%ROWTYPE;
    tLossReasonLimits TYPE_LossReasonLimits;
    rLotSizeLimits curLotSizeLimits%ROWTYPE;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Get the Bins Limits if required
    --=====================
    IF ServiceData.YieldLimits.BinsLimitsRequired = 1 THEN
        ServiceData.BinsLimits.DELETE;
        FOR rBinsLimits IN curBinsLimits LOOP
            tBinsLimits.LimitType := rBinsLimits.LimitType;
            tBinsLimits.LimitCondition := rBinsLimits.LimitCondition;
            tBinsLimits.LimitInPercentage := rBinsLimits.LimitInPercentage;
            tBinsLimits.LimitInUnits := rBinsLimits.LimitInUnits;
            tBinsLimits.Bins.DELETE;
            FOR rBinsLimitsDetails IN curBinsLimitsDetails(rBinsLimits.BinsLimitsId) LOOP
                tBinsLimits.Bins(
tBinsLimits.Bins.COUNT) := rBinsLimitsDetails.Bin;
            END LOOP;
            ServiceData.BinsLimits(ServiceData.BinsLimits.COUNT) := tBinsLimits;
        END LOOP;
    END IF;
    --=====================
    -- Get the Loss Reason Limits if required
    --=====================
    IF ServiceData.YieldLimits.LossReasonLimitsRequired = 1 THEN
        ServiceData.LossReasonLimits.DELETE;
        FOR rLossReasonLimits IN curLossReasonLimits LOOP
            tLossReasonLimits.LossReasonNames.DELETE;
            tLossReasonLimits.LossReasonId := rLossReasonLimits.LossReasonId;
            tLossReasonLimits.LossReasonName := rLossReasonLimits.LossReasonName;
            tLossReasonLimits.LimitCondition := rLossReasonLimits.LimitCondition;
            tLossReasonLimits.LimitInPercentage := rLossReasonLimits.LimitInPercentage;
            tLossReasonLimits.LimitInUnits := rLossReasonLimits.LimitInUnits;
            FOR rLossReasonLimitsDetails IN curLossReasonLimitsDetails(rLossReasonLimits.LossReasonLimitsId) LOOP
                tLossReasonLimits.LossReasonNames(tLossReasonLimits.LossReasonNames.COUNT) := rLossReasonLimitsDetails.LossReasonName;
            END LOOP;
            ServiceData.LossReasonLimits(ServiceData.LossReasonLimits.COUNT) := tLossReasonLimits;
        END LOOP;
    END IF;
    --=====================
    -- Get the Lot Size Limits if required
    --=====================
    IF ServiceData.YieldLimits.LotSizeLimitsRequired = 1 THEN
        OPEN curLotSizeLimits;
        FETCH curLotSizeLimits INTO rLotSizeLimits;
        IF NOT curLotSizeLimits%NOTFOUND THEN
            CLOSE curLotSizeLimits;
            ServiceData.LotSizeLimits.LotSize := rLotSizeLimits.LotSize;
            ServiceData.LotSizeLimits.LowerLimit := rLotSizeLimits.LowerLimit;
            ServiceData.LotSizeLimits.YieldCutLimit := rLotSizeLimits.YieldCutLimit;
        ELSE
            CLOSE curLotSizeLimits;
        END IF;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.GetOtherLimits->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.GetOtherLimits->' || SQLERRM);
END GetOtherLimits;

--=====================
-- InsertFailure
--=====================
PROCEDURE InsertFailure(InputData IN TYPE_YieldHandler_InputData
                        , ServiceData IN OUT TYPE_YieldHandler_ServiceData
                        , FailureType IN VARCHAR2
                        , FailureCondition IN VARCHAR2
                        , YieldValue NUMBER
                        , YieldLimit NUMBER
                        , FailureAction IN VARCHAR2
                        , EmailGroupId IN VARCHAR2
                        , Bins IN VARCHAR2 DEFAULT ''
                        , LossReasonName IN VARCHAR2 DEFAULT ''
                        , LossReasonNames IN VARCHAR2 DEFAULT '')
IS
    --=====================
    -- Input parameter variables
    --=====================
    vFailureType A_WIPLotFailures.FailureType%TYPE := FailureType;
    vFailureCondition A_WIPLotFailures.FailureCondition%TYPE := FailureCondition;
    nYieldLimit A_WIPLotFailures.YieldLimit%TYPE := YieldLimit;
    nYieldValue A_WIPLotFailures.YieldValue%TYPE := YieldValue;
    vFailureAction A_WIPLotFailures.FailureAction%TYPE := FailureAction;
    vEmailGroupId A_WIPLotFailures.EmailGroupId%TYPE := EmailGroupId;
    vBins A_WIPLotFailures.Bins%TYPE := Bins;
    vLossReasonName A_WIPLotFailures.LossReasonName%TYPE := LossReasonName;
    vLossReasonNames A_WIPLotFailures.LossReasonNames%TYPE := LossReasonNames;
    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Fetch the CDO Type Id if it is 0
    --=====================
    IF ServiceData.WIPLotFailuresCDOTypeId = 0 THEN
        ServiceData.WIPLotFailuresCDOTypeId := GetCDODefId('WIPLotFailuresTemp');
    END IF;
    --=====================
    -- Insert the yield record
    --=====================
    INSERT INTO A_WIPLotFailuresTemp
    (
        CDOTypeId
        , WIPLotFailuresTempId
        , ChangeCount
        , ContainerId
        , FailureCategory
        , FailureType
        , FailureCondition
        , YieldLimit
        , YieldValue
        , FailureAction
        , EmailGroupId
        , Bins
        , LossReasonName
        , LossReasonNames
    )
    VALUES
    (
        ServiceData.WIPLotFailuresCDOTypeId
        , SetInstanceId('WIPLotFailuresTemp')
        , 1
        , ServiceData.ContainerId
        , ServiceData.FailureCategory
        , vFailureType
        , vFailureCondition
        , nYieldLimit
        , nYieldValue
        , vFailureAction
        , vEmailGroupId
        , vBins
        , vLossReasonName
        , vLossReasonNames
    );
    --=====================
    -- Commit the record
    --=====================
    COMMIT;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.InsertFailures->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.InsertFailures->' || SQLERRM);
END InsertFailure;

--=====================
-- CheckLimits
--=====================
PROCEDURE CheckLimits (InputData IN TYPE_YieldHandler_InputData
                       , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check whether a limit is found
    --=====================
    IF ServiceData.YieldLimits.CriteriaId IS NOT NULL OR
       ServiceData.LotSizeLimits.LotSize > 0 OR
       ServiceData.BinsLimits.COUNT > 0 OR
       ServiceData.LossReasonLimits.COUNT > 0 THEN
        ServiceData.LimitsFound := 1;
        --=====================
        -- Check the Lot Size or Common limits
        --=====================
        IF ServiceData.YieldLimits.LotSizeLimitsRequired = 1 AND ServiceData.LotSizeLimits.LotSize > 0 THEN
            CheckLotSizeLimits (InputData, ServiceData);
            CheckCommonLimits (InputData, ServiceData, 1, 0, 0);
        ELSE
            CheckCommonLimits (InputData, ServiceData);
        END IF;
        --=====================
        -- Check the Bins Limits
        --=====================
        IF ServiceData.YieldLimits.BinsLimitsRequired = 1 THEN
            CheckBinsLimits (InputData, ServiceData);
        END IF;
        --=====================
        -- Check the Loss Reason Limits
        --=====================
        IF ServiceData.YieldLimits.LossReasonLimitsRequired = 1THEN
            CheckLossReasonLimits (InputData, ServiceData);
        END IF;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckLimits->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckLimits->' || SQLERRM);
END CheckLimits;

--=====================
-- CheckCommonLimits
--=====================
PROCEDURE CheckCommonLimits (InputData IN TYPE_YieldHandler_InputData
                             , ServiceData IN OUT TYPE_YieldHandler_ServiceData
                             , CheckUpperYieldLimit NUMBER DEFAULT 1
                             , CheckLowerYieldLimit NUMBER DEFAULT 1
                             , CheckYieldCutYieldLimit NUMBER DEFAULT 1)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Set the Failure Category
    --=====================
    IF ServiceData.FailureCategory IS NULL THEN
        ServiceData.FailureCategory := 'COMMON';
    END IF;
    --=====================
    -- Check the limits
    --=====================
    IF (CheckUpperYieldLimit = 1) AND (ServiceData.YieldLimits.UpperYieldLimit > 0) AND (ServiceData.YieldValue > ServiceData.YieldLimits.UpperYieldLimit) THEN
        InsertFailure (InputData
                       , ServiceData
                       , 'UPPER'
                       , '>'
                       , ServiceData.YieldValue
                       , ServiceData.YieldLimits.UpperYieldLimit
                       , ServiceData.YieldLimits.UpperYieldFailureAction
                       , NVL(ServiceData.YieldLimits.UpperYieldEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                      );
        ServiceData.FailCommonLImits := 1;
    END IF;
    IF (CheckLowerYieldLimit = 1) AND (ServiceData.YieldLimits.LowerYieldLimit > 0) AND (ServiceData.YieldValue <= ServiceData.YieldLimits.LowerYieldLimit) THEN
        InsertFailure (InputData
                       , ServiceData
                       , 'LOWER'
                       , '<='
                       , ServiceData.YieldValue
                       , ServiceData.YieldLimits.LowerYieldLimit
                       , ServiceData.YieldLimits.LowerYieldFailureAction
                       , NVL(ServiceData.YieldLimits.LowerYieldEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                      );
        ServiceData.FailCommonLImits := 1;
    END IF;
    IF (CheckYieldCutYieldLimit = 1) AND (ServiceData.YieldLimits.YieldCutYieldLimit > 0) AND (ServiceData.YieldValue <= ServiceData.YieldLimits.YieldCutYieldLimit) THEN
        InsertFailure (InputData
                       , ServiceData
                       , 'YIELDCUT'
                       , '<='
                       , ServiceData.YieldValue
                       , ServiceData.YieldLimits.YieldCutYieldLimit
                       , 'HOLD'
                       , NVL(ServiceData.YieldLimits.YieldCutYieldEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                      );
        ServiceData.FailCommonLImits := 1;
        ServiceData.FailYieldCut := 1;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckCommonLimits->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckCommonLimits->' || SQLERRM);
END CheckCommonLimits;

--=====================
-- CheckLotSizeLimits
--=====================
PROCEDURE CheckLotSizeLimits (InputData IN TYPE_YieldHandler_InputData
                              , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Set the Failure Category
    --=====================
    ServiceData.FailureCategory := 'LOTSIZE';
    --=====================
    -- Check the limits
    --=====================
    IF (ServiceData.LotSizeLimits.LowerLimit > 0) AND (ServiceData.TotalBadQty > ServiceData.LotSizeLimits.LowerLimit) THEN
        InsertFailure (InputData
                       , ServiceData
                       , 'LOWER'
                       , '>'
                       , ServiceData.TotalBadQty
                       , ServiceData.LotSizeLimits.LowerLimit
                       , ServiceData.YieldLimits.LotSizeLimitsFailureAction
                       , NVL(ServiceData.YieldLimits.LotSizeLowerLimitEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                      );
        ServiceData.FailLotSizeLImits := 1;
    END IF;
    IF (ServiceData.LotSizeLimits.YieldCutLimit > 0) AND (ServiceData.TotalBadQty > ServiceData.LotSizeLimits.YieldCutLimit) THEN
        InsertFailure (InputData
                       , ServiceData
                       , 'YIELDCUT'
                       , '>'
                       , ServiceData.TotalBadQty
                       , ServiceData.LotSizeLimits.YieldCutLimit
                       , ServiceData.YieldLimits.LotSizeLimitsFailureAction
                       , NVL(ServiceData.YieldLimits.LotSizeYieldCutEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                      );
        ServiceData.FailLotSizeLImits := 1;
        ServiceData.FailYieldCut := 1;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckLotSizeLimits->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckLotSizeLimits->' || SQLERRM);
END CheckLotSizeLimits;

--=====================
-- CheckLossReasonLimits
--=====================
PROCEDURE CheckLossReasonLimits (InputData IN TYPE_YieldHandler_InputData
                                 , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    tLossReasonLimits TYPE_LossReasonLimits;
    nCount NUMBER;
    nCount1 NUMBER;
    nCount2 NUMBER;
    nLossReasonQty NUMBER;
    vLossReasonNames A_WIPLotFailures.LossReasonNames%TYPE;
    nRejectYieldValue NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Set the Failure Category
    --=====================
    ServiceData.FailureCategory := 'LOSSREASON';
    --=====================
    -- Check the limits
    --=====================
    FOR nCount IN 0..ServiceData.LossReasonLimits.COUNT-1 LOOP
        --=====================
        -- Set the yield parameters
        --=====================
        tLossReasonLimits := ServiceData.LossReasonLimits(nCount);
        nLossReasonQty := 0;
        vLossReasonNames := '';
        IF tLossReasonLimits.LossReasonNames.COUNT > 0 THEN
            FOR nCount1 IN 0..tLossReasonLimits.LossReasonNames.COUNT-1 LOOP
                IF vLossReasonNames IS NULL THEN
                    vLossReasonNames := tLossReasonLimits.LossReasonNames(nCount1);
                ELSE
                    vLossReasonNames := vLossReasonNames || ',' || tLossReasonLimits.LossReasonNames(nCount1);
                END IF;
                FOR nCount2 IN 0..InputData.LossReasonNames.COUNT-1 LOOP
                    IF tLossReasonLimits.LossReasonNames(nCount1) = InputData.LossReasonNames(nCount2) THEN
                        nLossReasonQty := nLossReasonQty + ABS(InputData.LossReasonQtys(nCount2));
                    END IF;
                END LOOP;
            END LOOP;
        ELSE
            FOR nCount1 IN 0..InputData.LossReasonNames.COUNT-1 LOOP
                IF tLossReasonLimits.LossReasonName = InputData.LossReasonNames(nCount1) THEN
                    nLossReasonQty := InputData.LossReasonQtys(nCount1);
                    vLossReasonNames := tLossReasonLimits.LossReasonName;
                    EXIT;
                END IF;
            END LOOP;
        END IF;
        --=====================
        -- Verify the yield
        --=====================
        IF (tLossReasonLimits.LimitInPercentage > 0) THEN
            nRejectYieldValue := 100 * nLossReasonQty / ServiceData.YieldQty;
            IF ((tLossReasonLimits.LimitCondition = '>') AND (nRejectYieldValue > tLossReasonLimits.LimitInPercentage)) OR
               ((tLossReasonLimits.LimitCondition = '>=') AND (nRejectYieldValue >= tLossReasonLimits.LimitInPercentage)) OR
               ((tLossReasonLimits.LimitCondition = '<') AND (nRejectYieldValue < tLossReasonLimits.LimitInPercentage)) OR
               ((tLossReasonLimits.LimitCondition = '<=') AND (nRejectYieldValue <= tLossReasonLimits.LimitInPercentage)) OR
               ((tLossReasonLimits.LimitCondition = '=') AND (nRejectYieldValue = tLossReasonLimits.LimitInPercentage)) THEN
                InsertFailure (InputData
                               , ServiceData
                               , 'PERCENTAGE'
                               , tLossReasonLimits.LimitCondition
                               , nRejectYieldValue
                               , tLossReasonLimits.LimitInPercentage
                               , ServiceData.YieldLimits.LossReasonLimitsFailureAction
                               , NVL(ServiceData.YieldLimits.LossReasonLimitsEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                               , ''
                               , tLossReasonLimits.LossReasonName
                               , vLossReasonNames
                              );
                ServiceData.FailLossReasonLImits := 1;
            END IF;
        END IF;
        IF (tLossReasonLimits.LimitInUnits > 0) THEN
            nRejectYieldValue := nLossReasonQty;
            IF ((tLossReasonLimits.LimitCondition = '>') AND (nRejectYieldValue > tLossReasonLimits.LimitInUnits)) OR
               ((tLossReasonLimits.LimitCondition = '>=') AND (nRejectYieldValue >= tLossReasonLimits.LimitInUnits)) OR
               ((tLossReasonLimits.LimitCondition = '<') AND (nRejectYieldValue < tLossReasonLimits.LimitInUnits)) OR
               ((tLossReasonLimits.LimitCondition = '<=') AND (nRejectYieldValue <= tLossReasonLimits.LimitInUnits)) OR
               ((tLossReasonLimits.LimitCondition = '=') AND (nRejectYieldValue = tLossReasonLimits.LimitInUnits)) THEN
                InsertFailure (InputData
                               , ServiceData
                               , 'UNITS'
                               , tLossReasonLimits.LimitCondition
                               , nRejectYieldValue
                               , tLossReasonLimits.LimitInUnits
                               , ServiceData.YieldLimits.LossReasonLimitsFailureAction
                               , NVL(ServiceData.YieldLimits.LossReasonLimitsEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                               , ''
                               , tLossReasonLimits.LossReasonName
                               , vLossReasonNames
                              );
                ServiceData.FailLossReasonLImits := 1;
            END IF;
        END IF;
    END LOOP;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckLossReasonLimits->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckLossReasonLimits->' || SQLERRM);
END CheckLossReasonLimits;

--=====================
-- CheckBinsLimits
--=====================
PROCEDURE CheckBinsLimits (InputData IN TYPE_YieldHandler_InputData
                           , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================

    --=====================
    -- Local variables
    --=====================
    tBinsLimits TYPE_BinsLimits;
    nCount NUMBER;
    nCount1 NUMBER;
    nCount2 NUMBER;
    vBins VARCHAR2(255);
    nBinsQty NUMBER;
    nRELEASECount NUMBER := 0;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Set the Failure Category
    --=====================
    ServiceData.FailureCategory := 'BINS';
    --=====================
    -- Check the limits
    --=====================
    FOR nCount IN 0..ServiceData.BinsLimits.COUNT-1 LOOP
        tBinsLimits := ServiceData.BinsLimits(nCount);
        nBinsQty := 0;
        vBins := '';
        FOR nCount1 IN 0..tBinsLimits.Bins.COUNT-1 LOOP
            IF vBins IS NULL THEN
                vBins := tBinsLimits.Bins(nCount1);
            ELSE
                vBins := vBins || ',' || tBinsLimits.Bins(nCount1);
            END IF;
            FOR nCount2 IN 0..InputData.Bins.COUNT-1 LOOP
                IF tBinsLimits.Bins(nCount1) = InputData.Bins(nCount2) THEN
                    nBinsQty := nBinsQty + ABS(InputData.BinsQtys(nCount2));
                END IF;
            END LOOP;
        END LOOP;
        ServiceData.YieldValue := 100 * nBinsQty / ServiceData.YieldQty;
        IF tBinsLimits.LimitType = 'RELEASE' THEN
            IF (tBinsLimits.LimitInPercentage > 0) THEN
               IF ((tBinsLimits.LimitCondition = '>') AND (ServiceData.YieldValue > tBinsLimits.LimitInPercentage)) OR
                  ((tBinsLimits.LimitCondition = '>=') AND (ServiceData.YieldValue >= tBinsLimits.LimitInPercentage)) OR
                  ((tBinsLimits.LimitCondition = '<') AND (ServiceData.YieldValue < tBinsLimits.LimitInPercentage)) OR
                  ((tBinsLimits.LimitCondition = '<=') AND (ServiceData.YieldValue <= tBinsLimits.LimitInPercentage)) OR
                  ((tBinsLimits.LimitCondition = '=') AND (ServiceData.YieldValue = tBinsLimits.LimitInPercentage)) THEN
                   IF nRELEASECount > -1 THEN
                       nRELEASECount := nRELEASECount + 1;
                   END IF;
               ELSE
                    InsertFailure (InputData
                                   , ServiceData
                                   , tBinsLimits.LimitType || 'PERCENTAGE'
                                   , tBinsLimits.LimitCondition
                                   , ServiceData.YieldValue
                                   , tBinsLimits.LimitInPercentage
                                   , ServiceData.YieldLimits.LossReasonLimitsFailureAction
                                   , NVL(ServiceData.YieldLimits.LossReasonLimitsEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                                   , vBins
                                  );
                    ServiceData.FailBinsLImits := 1;
                    nRELEASECount := -1;
                END IF;
            END IF;
            IF (tBinsLimits.LimitInUnits > 0) THEN
               IF ((tBinsLimits.LimitCondition = '>') AND (nBinsQty > tBinsLimits.LimitInUnits)) OR
                  ((tBinsLimits.LimitCondition = '>=') AND (nBinsQty >= tBinsLimits.LimitInUnits)) OR
                  ((tBinsLimits.LimitCondition = '<') AND (nBinsQty < tBinsLimits.LimitInUnits)) OR
                  ((tBinsLimits.LimitCondition = '<=') AND (nBinsQty <= tBinsLimits.LimitInUnits)) OR
                  ((tBinsLimits.LimitCondition = '=') AND (nBinsQty = tBinsLimits.LimitInUnits)) THEN
                   IF nRELEASECount > -1 THEN
                       nRELEASECount := nRELEASECount + 1;
                   END IF;
               ELSE
                    InsertFailure (InputData
                                   , ServiceData
                                   , tBinsLimits.LimitType || 'UNITS'
                                   , tBinsLimits.LimitCondition
                                   , nBinsQty
                                   , tBinsLimits.LimitInUnits
                                   , ServiceData.YieldLimits.BinsLimitsFailureAction
                                   , NVL(ServiceData.YieldLimits.BinsLimitsEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                                   , vBins
                                  );
                    ServiceData.FailBinsLImits := 1;
                    nRELEASECount := -1;
                END IF;
            END IF;
        ELSE
            IF (tBinsLimits.LimitInPercentage > 0) THEN
               IF ((tBinsLimits.LimitCondition = '>') AND (ServiceData.YieldValue > tBinsLimits.LimitInPercentage)) OR
                  ((tBinsLimits.LimitCondition = '>=') AND (ServiceData.YieldValue >= tBinsLimits.LimitInPercentage)) OR
                  ((tBinsLimits.LimitCondition = '<') AND (ServiceData.YieldValue < tBinsLimits.LimitInPercentage)) OR
                  ((tBinsLimits.LimitCondition = '<=') AND (ServiceData.YieldValue <= tBinsLimits.LimitInPercentage)) OR
                  ((tBinsLimits.LimitCondition = '=') AND (ServiceData.YieldValue = tBinsLimits.LimitInPercentage)) THEN
                    InsertFailure (InputData
                                   , ServiceData
                                   , tBinsLimits.LimitType || 'PERCENTAGE'
                                   , tBinsLimits.LimitCondition
                                   , ServiceData.YieldValue
                                   , tBinsLimits.LimitInPercentage
                                   , ServiceData.YieldLimits.LossReasonLimitsFailureAction
                                   , NVL(ServiceData.YieldLimits.LossReasonLimitsEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                                   , vBins
                                  );
                    ServiceData.FailBinsLImits := 1;
                    IF tBinsLimits.LimitType = 'YIELDCUT' THEN
                        ServiceData.FailYieldCut := 1;
                    END IF;
                END IF;
            END IF;
            IF (tBinsLimits.LimitInUnits > 0) THEN
               IF ((tBinsLimits.LimitCondition = '>') AND (nBinsQty > tBinsLimits.LimitInUnits)) OR
                  ((tBinsLimits.LimitCondition = '>=') AND (nBinsQty >= tBinsLimits.LimitInUnits)) OR
                  ((tBinsLimits.LimitCondition = '<') AND (nBinsQty < tBinsLimits.LimitInUnits)) OR
                  ((tBinsLimits.LimitCondition = '<=') AND (nBinsQty <= tBinsLimits.LimitInUnits)) OR
                  ((tBinsLimits.LimitCondition = '=') AND (nBinsQty = tBinsLimits.LimitInUnits)) THEN
                    InsertFailure (InputData
                                   , ServiceData
                                   , tBinsLimits.LimitType || 'UNITS'
                                   , tBinsLimits.LimitCondition
                                   , nBinsQty
                                   , tBinsLimits.LimitInUnits
                                   , ServiceData.YieldLimits.BinsLimitsFailureAction
                                   , NVL(ServiceData.YieldLimits.BinsLimitsEmailGroupId, ServiceData.YieldFailureEmailGroupId)
                                   , vBins
                                  );
                    ServiceData.FailBinsLImits := 1;
                    IF tBinsLimits.LimitType = 'YIELDCUT' THEN
                        ServiceData.FailYieldCut := 1;
                    END IF;
                END IF;
            END IF;
        END IF;
    END LOOP;
    --=====================
    -- Check whether to revert the Lot Size or Common Limits failure
    --=====================
    IF ServiceData.FailCommonLimits = 1 AND ServiceData.FailYieldCut = 0 AND ServiceData.FailBinsLImits = 0 AND nRELEASECount > 0 THEN
        ServiceData.FailCommonLimits := -1;
    END IF;
    IF ServiceData.FailLotSizeLimits = 1 AND ServiceData.FailYieldCut = 0 AND ServiceData.FailBinsLImits = 0 AND nRELEASECount > 0 THEN
        ServiceData.FailLotSizeLimits := -1;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckBinsLimits->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.CheckBinsLimits->' || SQLERRM);
END CheckBinsLimits;

--=====================
-- ExecuteASSEMBLY
--=====================
PROCEDURE ExecuteASSEMBLY (InputData IN TYPE_YieldHandler_InputData
                           , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curYieldLimits1 IS
    SELECT
        AssemblyYieldLimitsId CriteriaId
        , UpperYieldLimit
        , UpperYieldFailureAction
        , UpperYieldEmailGroupId
        , LowerYieldLimit
        , LowerYieldFailureAction
        , LowerYieldEmailGroupId
        , YieldCutYieldLimit
        , YieldCutYieldEmailGroupId
        , BinsLimitsRequired
        , BinsLimitsFailureAction
        , BinsLimitsEmailGroupId
        , LossReasonLimitsRequired
        , LossReasonLimitsFailureAction
        , LossReasonLimitsEmailGroupId
        , LotSizeLimitsRequired
        , LotSizeLimitsFailureAction
        , LotSizeLowerLimitEmailGroupId
        , LotSizeYieldCutEmailGroupId
    FROM
        A_AssemblyYieldLimits
    WHERE
        YieldTypeId = ServiceData.YieldTypeId
        AND SpecId = ServiceData.SpecId
        AND ObjectType = 'ASSEMBLY1'
        AND (OwnerId = ServiceData.OwnerId OR OwnerId IS NULL)
        AND (ProcessSpecId = ServiceData.ProcessSpecId OR ProcessSpecId IS NULL)
        AND (Device = ServiceData.Device OR Device IS NULL)
        AND (DeviceVersion = ServiceData.DeviceVersion OR DeviceVersion IS NULL)
        AND (PackageType = ServiceData.PackageType OR PackageType IS NULL)
        AND (Leads = ServiceData.Leads OR Leads IS NULL)
    ORDER BY
        DECODE(OwnerId, NULL, 0, 1) DESC
        , DECODE(ProcessSpecId, NULL, 0, 1) DESC
        , DECODE(Device, NULL, 0, 1) DESC
        , DECODE(DeviceVersion, NULL, 0, 1) DESC
        , DECODE(PackageType, NULL, 0, 1) DESC
        , DECODE(Leads, NULL, 0, 1) DESC
    ;
    CURSOR curYieldLimits2 IS
    SELECT
        AssemblyYieldLimitsId CriteriaId
        , UpperYieldLimit
        , UpperYieldFailureAction
        , UpperYieldEmailGroupId
        , LowerYieldLimit
        , LowerYieldFailureAction
        , LowerYieldEmailGroupId
        , YieldCutYieldLimit
        , YieldCutYieldEmailGroupId
        , BinsLimitsRequired
        , BinsLimitsFailureAction
        , BinsLimitsEmailGroupId
        , LossReasonLimitsRequired
        , LossReasonLimitsFailureAction
        , LossReasonLimitsEmailGroupId
        , LotSizeLimitsRequired
        , LotSizeLimitsFailureAction
        , LotSizeLowerLimitEmailGroupId
        , LotSizeYieldCutEmailGroupId
    FROM
        A_AssemblyYieldLimits
    WHERE
        YieldTypeId = ServiceData.YieldTypeId
        AND OperationGroupId = ServiceData.OperationGroupId
        AND ObjectType = 'ASSEMBLY2'
        AND (OwnerId = ServiceData.OwnerId OR OwnerId IS NULL)
        AND (ProcessSpecId = ServiceData.ProcessSpecId OR ProcessSpecId IS NULL)
        AND (Device = ServiceData.Device OR Device IS NULL)
        AND (DeviceVersion = ServiceData.DeviceVersion OR DeviceVersion IS NULL)
        AND (PackageType = ServiceData.PackageType OR PackageType IS NULL)
        AND (Leads = ServiceData.Leads OR Leads IS NULL)
    ORDER BY
        DECODE(OwnerId, NULL, 0, 1) DESC
        , DECODE(ProcessSpecId, NULL, 0, 1) DESC
        , DECODE(Device, NULL, 0, 1) DESC
        , DECODE(DeviceVersion, NULL, 0, 1) DESC
        , DECODE(PackageType, NULL, 0, 1) DESC
        , DECODE(Leads, NULL, 0, 1) DESC
    ;
    CURSOR curOperationGroupMoveInQty IS
    SELECT
        A.MoveInQty MoveInQty
    FROM
        HistoryMainline A
        , MoveHistory B
        , Spec C
        , Operation D
    WHERE
        A.HistoryMainlineId = B.HistoryMainlineId
        AND B.ToSpecId = C.SpecId
        AND C.OperationId = D.OperationId
        AND A.ContainerId = ServiceData.ContainerId
        AND D.OperationGroupId = ServiceData.OperationGroupId
    ORDER BY
        A.TxnDate
    ;
    --=====================
    -- Local variables
    --=====================
    nTotalMoveInQty NUMBER;
    nTotalMoveOutQty NUMBER;
    rOperationGroupMoveInQty curOperationGroupMoveInQty%ROWTYPE;
    nBaseQty NUMBER;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check criteria 1 limits (by Spec)
    --=====================
    OPEN curYieldLimits1;
    FETCH curYieldLimits1 INTO ServiceData.YieldLimits;
    IF NOT curYieldLimits1%NOTFOUND AND ServiceData.LotType <> 'R' THEN
        CLOSE curYieldLimits1;
        GetOtherLimits (InputData, ServiceData);
        ServiceData.FailureCategory := 'COMMON1';
        IF ServiceData.ProcessSpecObjectType = 'ASSEMBLYMOTHERLOT' THEN
            ServiceData.YieldValue := 100 * (ServiceData.YieldQty - ServiceData.TotalBadQty) / ServiceData.YieldQty;
        ELSIF ServiceData.ProcessSpecObjectType = 'ASSEMBLYSUBLOT' THEN
            IF ServiceData.StepLogicName = 'PACKING' THEN
                SELECT
                    ActualScheduleQty
                INTO
                    nBaseQty
                FROM
                    A_ScheduleData
                WHERE
                    ScheduleDataId = ServiceData.ScheduleDataId
                ;
                ServiceData.YieldValue := 100 * (ServiceData.YieldQty - ServiceData.TotalBadQty) / nBaseQty;
            ELSE
                ServiceData.YieldValue := 100 * (ServiceData.YieldQty - ServiceData.TotalBadQty) / ServiceData.YieldQty;
            END IF;
        ELSE -- i.e. ASSEMBLYCARRIER
            ServiceData.YieldValue := 100 * (ServiceData.YieldQty - ServiceData.TotalBadQty) / ServiceData.YieldQty;
        END IF;
        CheckLimits (InputData, ServiceData);
    ELSE
        CLOSE curYieldLimits1;
    END IF;
    --=====================
    -- Check criteria 2 limits (by Operation Group)
    --=====================
    IF InputData.OperationGroupIsChanged = '1' THEN
        OPEN curYieldLimits2;
        FETCH curYieldLimits2 INTO ServiceData.YieldLimits;
        IF NOT curYieldLimits2%NOTFOUND THEN
            CLOSE curYieldLimits2;
            ServiceData.FailureCategory := 'COMMON2';
            IF ServiceData.ProcessSpecObjectType = 'ASSEMBLYMOTHERLOT' OR ServiceData.ProcessSpecObjectType = 'ASSEMBLYSUBLOT 'THEN
                OPEN curOperationGroupMoveInQty;
                FETCH curOperationGroupMoveInQty INTO rOperationGroupMoveInQty;
                IF curOperationGroupMoveInQty%FOUND THEN
                    IF rOperationGroupMoveInQty.MoveInQty > 0 THEN
                        nBaseQty := rOperationGroupMoveInQty.MoveInQty;
                    ELSE
                        --nBaseQty := ServiceData.YieldQty;
                        nBaseQty := ServiceData.OriginalQty;
                    END IF;
                ELSE
                    --nBaseQty := ServiceData.YieldQty;
                    nBaseQty := ServiceData.OriginalQty;
                END IF;
                ServiceData.YieldValue := 100 * (ServiceData.YieldQty - ServiceData.TotalBadQty) / nBaseQty;
            ELSE -- For ASSEMBLYCARRIER
                SELECT
                    TotalMoveInQty
                    , TotalMoveOutQty
                INTO
                    nTotalMoveInQty
                    , nTotalMoveOutQty
                FROM
                    A_ScheduleMoveData
                WHERE
                    ScheduleDataId = ServiceData.ScheduleDataId
                    AND SpecId = ServiceData.SpecId
                ;
                ServiceData.YieldValue := 100 * (nTotalMoveOutQty + ServiceData.YieldQty - ServiceData.TotalBadQty) / nTotalMoveInQty;
            END IF;
            CheckLimits (InputData, ServiceData);
        ELSE
            CLOSE curYieldLimits2;
        END IF;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecuteASSEMBLY->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecuteASSEMBLY->' || SQLERRM);
END ExecuteASSEMBLY;

--=====================
-- ExecuteBURNIN
--=====================
PROCEDURE ExecuteBURNIN (InputData IN TYPE_YieldHandler_InputData
                         , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curYieldLimits IS
    SELECT
        TestYieldLimitsId CriteriaId
        , UpperYieldLimit
        , UpperYieldFailureAction
        , UpperYieldEmailGroupId
        , LowerYieldLimit
        , LowerYieldFailureAction
        , LowerYieldEmailGroupId
        , YieldCutYieldLimit
        , YieldCutYieldEmailGroupId
        , BinsLimitsRequired
        , BinsLimitsFailureAction
        , BinsLimitsEmailGroupId
        , LossReasonLimitsRequired
        , LossReasonLimitsFailureAction
        , LossReasonLimitsEmailGroupId
        , LotSizeLimitsRequired
        , LotSizeLimitsFailureAction
        , LotSizeLowerLimitEmailGroupId
        , LotSizeYieldCutEmailGroupId
    FROM
        A_TestYieldLimits
    WHERE
        YieldTypeId = ServiceData.YieldTypeId
        AND SpecId = ServiceData.SpecId
        AND ProductLineId = ServiceData.ProductLineId
        AND (Device = ServiceData.Device OR Device IS NULL)
        AND (DeviceVersion = SUBSTR(ServiceData.DeviceVersion, 0, LENGTH(NVL(DeviceVersion, ''))) OR DeviceVersion IS NULL)
        AND (PackageType = ServiceData.PackageType OR PackageType IS NULL)
        AND (Leads = ServiceData.Leads OR Leads IS NULL)
        AND (BucketNumber = ServiceData.BucketNumber OR BucketNumber IS NULL)
    ORDER BY
        DECODE(Device, NULL, 0, 1) DESC
        , (DECODE(DeviceVersion, NULL, 0, 1) + LENGTH(DeviceVersion)) DESC
        , DECODE(PackageType, NULL, 0, 1) DESC
        , DECODE(Leads, NULL, 0, 1) DESC
        , DECODE(BucketNumber, NULL, 0, 1) DESC
    ;
    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check the limits
    --=====================
    OPEN curYieldLimits;
    FETCH curYieldLimits INTO ServiceData.YieldLimits;
    IF NOT curYieldLimits%NOTFOUND AND InputData.LotType <> 'R' THEN
        CLOSE curYieldLimits;
        GetOtherLimits (InputData, ServiceData);
        CheckLimits (InputData, ServiceData);
    ELSE
        CLOSE curYieldLimits;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecuteBURNIN->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecuteBURNIN->' || SQLERRM);
END ExecuteBURNIN;

--=====================
-- ExecuteTMP
--=====================
PROCEDURE ExecuteTMP (InputData IN TYPE_YieldHandler_InputData
                      , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curYieldLimits IS
    SELECT
        TestYieldLimitsId CriteriaId
        , UpperYieldLimit
        , UpperYieldFailureAction
        , UpperYieldEmailGroupId
        , LowerYieldLimit
        , LowerYieldFailureAction
        , LowerYieldEmailGroupId
        , YieldCutYieldLimit
        , YieldCutYieldEmailGroupId
        , BinsLimitsRequired
        , BinsLimitsFailureAction
        , BinsLimitsEmailGroupId
        , LossReasonLimitsRequired
        , LossReasonLimitsFailureAction
        , LossReasonLimitsEmailGroupId
        , LotSizeLimitsRequired
        , LotSizeLimitsFailureAction
        , LotSizeLowerLimitEmailGroupId
        , LotSizeYieldCutEmailGroupId
        , Processspecid
    FROM
        A_TestYieldLimits
    WHERE
        YieldTypeId = ServiceData.YieldTypeId
        AND SpecId = ServiceData.SpecId
        AND (ProductLineId = ServiceData.ProductLineId OR ProductLineId IS NULL)
        AND (ProcessSpecId = ServiceData.ProcessSpecId OR ProcessSpecId IS NULL)
        AND (Device = ServiceData.Device OR Device IS NULL)
        AND (DeviceVersion = SUBSTR(ServiceData.DeviceVersion, 0, LENGTH(NVL(DeviceVersion, ''))) OR DeviceVersion IS NULL)
        AND (PackageType = ServiceData.PackageType OR PackageType IS NULL)
        AND (Leads = ServiceData.Leads OR Leads IS NULL)
        AND (TestProgramName = ServiceData.TestProgramName OR TestProgramName IS NULL)
        AND (FabLotNumber = ServiceData.FabLotNumber OR FabLotNumber IS NULL)
        AND (OperatingRange = ServiceData.OperatingRange OR OperatingRange IS NULL)
        AND (AssemblyPlant = ServiceData.AssemblyPlant OR AssemblyPlant IS NULL)
    ORDER BY
        DECODE(ProductLineId, NULL, 0, 1) DESC
        , DECODE(ProcessSpecId, NULL, 0, 1) DESC
        , DECODE(Device, NULL, 0, 1) DESC
        , (DECODE(DeviceVersion, NULL, 0, 1) + LENGTH(DeviceVersion)) DESC
        , DECODE(PackageType, NULL, 0, 1) DESC
        , DECODE(Leads, NULL, 0, 1) DESC
        , DECODE(TestProgramName, NULL, 0, 1) DESC
        , DECODE(FabLotNumber, NULL, 0, 1) DESC
        , DECODE(OperatingRange, NULL, 0, 1) DESC
        , DECODE(AssemblyPlant, NULL, 0, 1) DESC
    ;
    --=====================
    -- Local variables
    --=====================
    v_YieldProcessSpecID A_TestYieldLimits.Processspecid%TYPE;

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check the limits
    --=====================
    OPEN curYieldLimits;
    FETCH curYieldLimits
    INTO  ServiceData.YieldLimits.CriteriaId
        , ServiceData.YieldLimits.UpperYieldLimit
        , ServiceData.YieldLimits.UpperYieldFailureAction
        , ServiceData.YieldLimits.UpperYieldEmailGroupId
        , ServiceData.YieldLimits.LowerYieldLimit
        , ServiceData.YieldLimits.LowerYieldFailureAction
        , ServiceData.YieldLimits.LowerYieldEmailGroupId
        , ServiceData.YieldLimits.YieldCutYieldLimit
        , ServiceData.YieldLimits.YieldCutYieldEmailGroupId
        , ServiceData.YieldLimits.BinsLimitsRequired
        , ServiceData.YieldLimits.BinsLimitsFailureAction
        , ServiceData.YieldLimits.BinsLimitsEmailGroupId
        , ServiceData.YieldLimits.LossReasonLimitsRequired
        , ServiceData.YieldLimits.LossReasonLimitsFailureAction
        , ServiceData.YieldLimits.LossReasonLimitsEmailGroupId
        , ServiceData.YieldLimits.LotSizeLimitsRequired
        , ServiceData.YieldLimits.LotSizeLimitsFailureAction
        , ServiceData.YieldLimits.LotSizeLowerLimitEmailGroupId
        , ServiceData.YieldLimits.LotSizeYieldCutEmailGroupId
        , v_YieldProcessSpecID;
    IF NOT curYieldLimits%NOTFOUND THEN
        CLOSE curYieldLimits;
        IF ServiceData.ContainerId <> '4802378000177bf5' THEN
        IF ServiceData.LotType = 'R' THEN
          IF (v_YieldProcessSpecID IS NOT NULL AND InputData.SpecName NOT IN ('7010','7015','7063','7065','7077'))
             -- Hard code to enforce check PN in SPAS SQ00193417: 54-90-14313-2GAA,54-90-14314-2GAA,54-90-14484-2GAA
             OR (ServiceData.ProductId IN ('480032800002ca4d','480032800002c98f','480032800002c994'
             ) AND InputData.SpecName IN ('7015','7010')) THEN
            GetOtherLimits (InputData, ServiceData);
            CheckLimits (InputData, ServiceData);
          END IF;
        ELSIF ServiceData.LotType = 'E' THEN
          IF InputData.SpecName NOT IN ('7063','7065','7077') THEN
            GetOtherLimits (InputData, ServiceData);
            CheckLimits (InputData, ServiceData);
          END IF;        
        ELSIF ServiceData.LotType = 'A' THEN
          IF InputData.SpecName NOT IN ('7010','7015','7063','7065','7077') THEN
            GetOtherLimits (InputData, ServiceData);
            CheckLimits (InputData, ServiceData);
          END IF;        
        ELSIF ServiceData.LotType = 'P' THEN
          IF InputData.SpecName NOT IN ('7063','7065','7077') THEN
            GetOtherLimits (InputData, ServiceData);
            CheckLimits (InputData, ServiceData);
          END IF;        
        ELSE
          GetOtherLimits (InputData, ServiceData);
          CheckLimits (InputData, ServiceData);
        END IF;
        END IF;
    ELSE
        CLOSE curYieldLimits;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecuteTMP->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecuteTMP->' || SQLERRM);
END ExecuteTMP;

--=====================
-- ExecuteSORTING
--=====================
PROCEDURE ExecuteSORTING (InputData IN TYPE_YieldHandler_InputData
                           , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curYieldLimits IS
    SELECT
        waferSortYieldLimitsId CriteriaId
        , UpperYieldLimit
        , UpperYieldFailureAction
        , UpperYieldEmailGroupId
        , LowerYieldLimit
        , LowerYieldFailureAction
        , LowerYieldEmailGroupId
        , YieldCutYieldLimit
        , YieldCutYieldEmailGroupId
        , BinsLimitsRequired
        , BinsLimitsFailureAction
        , BinsLimitsEmailGroupId
        , LossReasonLimitsRequired
        , LossReasonLimitsFailureAction
        , LossReasonLimitsEmailGroupId
        , LotSizeLimitsRequired
        , LotSizeLimitsFailureAction
        , LotSizeLowerLimitEmailGroupId
        , LotSizeYieldCutEmailGroupId
    FROM
        A_WaferSortYieldLimits
    WHERE
        YieldTypeId = ServiceData.YieldTypeId
        AND SpecId = ServiceData.SpecId
        AND Device = ServiceData.Device
        AND DeviceVersion = ServiceData.DeviceVersion
    ;
    --=====================
    -- Local variables
    --=====================

    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check the limits
    --=====================
    OPEN curYieldLimits;
    FETCH curYieldLimits INTO ServiceData.YieldLimits;
    IF NOT curYieldLimits%NOTFOUND AND InputData.LotType <> 'R' THEN
        CLOSE curYieldLimits;
        GetOtherLimits (InputData, ServiceData);
        CheckLimits (InputData, ServiceData);
    ELSE
        CLOSE curYieldLimits;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecuteSORTING->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecuteSORTING->' || SQLERRM);
END ExecuteSORTING;

--=====================
-- ExecutePDA
--=====================
PROCEDURE ExecutePDA (InputData IN TYPE_YieldHandler_InputData
                      , ServiceData IN OUT TYPE_YieldHandler_ServiceData)
IS
    --=====================
    -- Input parameter variables
    --=====================

    --=====================
    -- Cursor Declarations
    --=====================
    CURSOR curPDALimits IS
    SELECT
        PDALimitsId CriteriaId
        , LossReasonsSpecId
        , ReferenceSpecId
    FROM
        A_PDALimits
    WHERE
        ProductLineId = ServiceData.ProductLineId
        AND SpecId = ServiceData.SpecId
        AND (PackageType = ServiceData.PackageType OR PackageType IS NULL)
        AND (Leads = ServiceData.Leads OR Leads IS NULL)
        AND (Device = ServiceData.Device OR Device IS NULL)
        AND (DeviceVersion = SUBSTR(ServiceData.DeviceVersion, 0, LENGTH(NVL(DeviceVersion, ''))) OR DeviceVersion IS NULL)
        AND (BucketNumber = ServiceData.BucketNumber OR BucketNumber IS NULL)
        AND (OPN = ServiceData.OPN OR OPN IS NULL)
    ORDER BY
        DECODE(PackageType, NULL, 0, 1) DESC
        , DECODE(Leads, NULL, 0, 1) DESC
        , DECODE(Device, NULL, 0, 1) DESC
        , (DECODE(DeviceVersion, NULL, 0, 1) + LENGTH(DeviceVersion)) DESC
        , DECODE(BucketNumber, NULL, 0, 1) DESC
        , DECODE(OPN, NULL, 0, 1) DESC
    ;
    CURSOR curPDABins (IN_PDALimitsId A_PDALimits.PDALimitsId%TYPE) IS
    SELECT
        Bin
        , NVL(IsNegative, 0) IsNegative
    FROM
        A_PDABins
    WHERE
        PDALimitsId = IN_PDALimitsId
    ORDER BY
        Bin
    ;
    CURSOR curLotBins (IN_Bin A_LotBins.BIN%TYPE) IS
    SELECT
        BinQty
    FROM
        A_LotBins
    WHERE
        ContainerId = ServiceData.ContainerId
        AND Bin = IN_Bin
    ;
    CURSOR curPDALossReasons (IN_PDALimitsId A_PDALimits.PDALimitsId%TYPE) IS
    SELECT
        A.LossReasonId LossReasonId
        , B.LossReasonName LossReasonName
        , NVL(A.IsNegative, 0) IsNegative
    FROM
        A_PDALossReasons A
        , LOssReason B
    WHERE
        A.LossReasonId = B.LossReasonId
        AND A.PDALimitsId = IN_PDALimitsId
    ;
    CURSOR curWIPLotHistory (IN_SpecId A_WIPLotHistory.SpecId%TYPE) IS
    SELECT
        WIPLotHistoryId
        , DECODE(MoveInQty, 0, MoveInQty2, MoveInQty) MoveInQty
    FROM
        A_WIPLotHistory
    WHERE
        IsLastInsertion = 1
        AND ContainerId = ServiceData.ContainerId
        AND SpecId = IN_SpecId
    ORDER BY
        CreationTimestamp DESC
    ;
    CURSOR curWIPLotRejectsHistory (IN_WIPLotHistoryId A_WIPLotDetailsHistory.WIPLotHistoryId%TYPE
                                    , IN_LossReasonId A_WIPLotRejectsHistory.LossReasonId%TYPE) IS
    SELECT
        NVL(SUM(B.RejectQty + B.DefectQty - B.BonusBackRejectQty), 0) LossReasonsQty
    FROM
        A_WIPLotDetailsHistory A
        , A_WIPLotRejectsHistory B
    WHERE
        A.WIPLotDetailsHistoryId = B.WIPLotDetailsHistoryId
        AND A.WIPLotHistoryId = IN_WIPLotHistoryId
        AND B.LossReasonId = IN_LossReasonId
    ;
    CURSOR curSmallLotLimits (IN_PDALimitsId A_PDALimits.PDALimitsId%TYPE) IS
    SELECT
        B.LimitInPercentage LimitInPercentage
        , B.LimitInUnits LimitInUnits
    FROM
        A_PDASmallLotLimits A
        , A_PDASmallLotLimitsDetails B
    WHERE
        A.PDASmallLotLimitsId = B.PDASmallLotLimitsId
        AND A.PDALimitsId = IN_PDALimitsId
        AND A.StepCount <= ServiceData.StepCount
        AND B.LotSize >= InputData.MainQty
    ORDER BY
        A.StepCount DESC
        , B.LotSize ASC
    ;
    CURSOR curUpperLimits (IN_PDALimitsId A_PDALimits.PDALimitsId%TYPE) IS
    SELECT
        UpperLimit
    FROM
        A_PDAUpperLimits
    WHERE
        PDALimitsId = IN_PDALimitsId
        AND StepCount <= ServiceData.StepCount
    ORDER BY
        StepCount DESC
    ;
    --=====================
    -- Local variables
    --=====================
    rPDABins curPDABins%ROWTYPE;
    rLotBins curLotBins%ROWTYPE;
    rPDALossReasons curPDALossReasons%ROWTYPE;
    rWIPLotHistory curWIPLotHistory%ROWTYPE;
    rWIPLotRejectsHistory curWIPLotRejectsHistory%ROWTYPE;
    rSmallLotLimits curSmallLotLimits%ROWTYPE;
    rUpperLimits curUpperLimits%ROWTYPE;
    nCount NUMBER;
    vBins VARCHAR2(255);
    nBinsQty NUMBER := 0;
    nLossReasonsQty NUMBER := 0;
    nMoveInQty NUMBER := 0;
    nLimitInPercentage NUMBER := -1;
    nLimitInUnits NUMBER := -1;
    VFailureType A_WIPLotFailures.FailureType%TYPE;
    nYieldValue NUMBER := 0;
    --=====================
    -- Pragma Declarations
    --=====================
    eExit EXCEPTION;
    PRAGMA EXCEPTION_INIT (eExit, -20000);
    eAppError EXCEPTION;
    PRAGMA EXCEPTION_INIT (eAppError, -20005);
BEGIN
    --=====================
    -- Check the limits
    --=====================
    OPEN curPDALimits;
    FETCH curPDALimits INTO ServiceData.PDALimits;
    IF NOT curPDALimits%NOTFOUND THEN
        CLOSE curPDALimits;
        --=====================
        -- Set some limits data
        --=====================
        ServiceData.LimitsFound := 1;
        ServiceData.FailureCategory := 'PDA';
        --=====================
        -- Set the bins data
        --=====================
        FOR rPDABins IN curPDABins(ServiceData.PDALimits.CriteriaId) LOOP
            IF InputData.Bins.COUNT > 0 THEN
                FOR nCount IN 0..InputData.Bins.COUNT-1 LOOP
                    IF InputData.Bins(nCount) = rPDABins.Bin THEN
                        vBins := vBins || InputData.Bins(nCount) || ',';
                        IF rPDABins.IsNegative = 1 THEN
                            nBinsQty := nBinsQty - ABS(InputData.BinsQtys(nCount));
                        ELSE
                            nBinsQty := nBinsQty + ABS(InputData.BinsQtys(nCount));
                        END IF;
                    END IF;
                END LOOP;
            ELSE
                OPEN curLotBins (rPDABins.Bin);
                FETCH curLotBins INTO rLotBins;
                IF NOT curLotBins%NOTFOUND THEN
                    CLOSE curLotBins;
                    vBins := vBins || rPDABins.Bin || ',';
                    IF rPDABins.IsNegative = 1 THEN
                        nBinsQty := nBinsQty - ABS(rLotBins.BinQty);
                    ELSE
                        nBinsQty := nBinsQty + ABS(rLotBins.BinQty);
                    END IF;
                ELSE
                    CLOSE curLotBins;
                END IF;
            END IF;
        END LOOP;
        --=====================
        -- Set the loss reason data
        --=====================
        IF ServiceData.PDALimits.LossReasonsSpecId IS NOT NULL THEN
            IF ServiceData.PDALimits.LossReasonsSpecId = ServiceData.SpecId THEN
                FOR rPDALossReasons IN curPDALossReasons(ServiceData.PDALimits.CriteriaId) LOOP
                    FOR nCount IN 0..InputData.LossReasonNames.COUNT-1 LOOP
                        IF InputData.LossReasonNames(nCount) = rPDALossReasons.LossReasonName THEN
                            IF rPDALossReasons.IsNegative = 1 THEN
                                nLossReasonsQty := nLossReasonsQty - (InputData.RejectQtys(nCount) + InputData.DefectQtys(nCount) - InputData.BonusBackRejectQtys(nCount));
                            ELSE
                                nLossReasonsQty := nLossReasonsQty + InputData.RejectQtys(nCount) + InputData.DefectQtys(nCount) - InputData.BonusBackRejectQtys(nCount);
                            END IF;
                        END IF;
                    END LOOP;
                END LOOP;
            ELSE
                OPEN curWIPLotHistory (ServiceData.PDALimits.LossReasonsSpecId);
                FETCH curWIPLotHistory INTO rWIPLotHistory;
                IF NOT curWIPLotHistory%NOTFOUND THEN
                    CLOSE curWIPLotHistory;
                    nMoveInQty := rWIPLotHistory.MoveInQty;
                    FOR rPDALossReasons IN curPDALossReasons(ServiceData.PDALimits.CriteriaId) LOOP
                        OPEN curWIPLotRejectsHistory(rWIPLotHistory.WIPLotHistoryId, rPDALossReasons.LossReasonId);
                        FETCH curWIPLotRejectsHistory INTO rWIPLotRejectsHistory;
                        IF NOT curWIPLotRejectsHistory%NOTFOUND THEN
                            CLOSE curWIPLotRejectsHistory;
                            IF rPDALossReasons.IsNegative = 1 THEN
                                nLossReasonsQty := nLossReasonsQty - rWIPLotRejectsHistory.LossReasonsQty;
                            ELSE
                                nLossReasonsQty := nLossReasonsQty + rWIPLotRejectsHistory.LossReasonsQty;
                            END IF;
                        ELSE
                            CLOSE curWIPLotRejectsHistory;
                        END IF;
                    END LOOP;
                ELSE
                    CLOSE curWIPLotHistory;
                END IF;
            END IF;
        END IF;
        --=====================
        -- Set the Move In Qty
        --=====================
        IF (ServiceData.PDALimits.ReferenceSpecId = ServiceData.SpecId) OR (ServiceData.PDALimits.ReferenceSpecId IS NULL) THEN
            nMoveInQty := ServiceData.MoveInQty;
        ELSE
            IF nMoveInQty = 0 THEN
                OPEN curWIPLotHistory (ServiceData.PDALimits.ReferenceSpecId);
                FETCH curWIPLotHistory INTO rWIPLotHistory;
                IF NOT curWIPLotHistory%NOTFOUND THEN
                    CLOSE curWIPLotHistory;
                    nMoveInQty := rWIPLotHistory.MoveInQty;
                ELSE
                    CLOSE curWIPLotHistory;
                END IF;
            END IF;
        END IF;
        IF nMoveInQty = 0 THEN
            nMoveInQty := InputData.MainQty;
        END IF;
        --=====================
        -- Get the limits
        --=====================
        OPEN curSmallLotLimits(ServiceData.PDALimits.CriteriaId);
        FETCH curSmallLotLimits INTO rSmallLotLimits;
        IF NOT curSmallLotLimits%NOTFOUND THEN
            CLOSE curSmallLotLimits;
            nLimitInPercentage := NVL(rSmallLotLimits.LimitInPercentage, -1);
            nLimitInUnits := NVL(rSmallLotLimits.LimitInUnits, -1);
            VFailureType := 'SMALLLOT';
        ELSE
            CLOSE curSmallLotLimits;
            VFailureType := 'UPPER';
            OPEN curUpperLimits(ServiceData.PDALimits.CriteriaId);
            FETCH curUpperLimits INTO rUpperLimits;
            IF NOT curUpperLimits%NOTFOUND THEN
                CLOSE curUpperLimits;
                nLimitInPercentage := NVL(rUpperLimits.UpperLimit, -1);
            ELSE
                CLOSE curUpperLimits;
            END IF;
        END IF;
        --=====================
        -- Check the PDA limits
        --=====================
        IF nLimitInPercentage >= 0 THEN
            nYieldValue := ABS(100 * (nBinsQty + nLossReasonsQty) / nMoveInQty);
            IF nYieldValue > nLimitInPercentage THEN
                ServiceData.FailPDALimits := 1;
                InsertFailure (InputData
                               , ServiceData
                               , VFailureType || 'PERCENTAGE'
                               , '>'
                               , nYieldValue
                               , nLimitInPercentage
                               , 'HOLD'
                               , ''
                               , vBins
                              );
            END IF;
        END IF;
        IF nLimitInUnits >= 0 THEN
            nYieldValue := ABS(nBinsQty + nLossReasonsQty);
            IF nYieldValue > nLimitInUnits THEN
                ServiceData.FailPDALimits := 1;
                InsertFailure (InputData
                               , ServiceData
                               , VFailureType || 'UNITS'
                               , '>'
                               , nYieldValue
                               , nLimitInUnits
                               , 'HOLD'
                               , ''
                               , vBins
                              );
            END IF;
        END IF;
    ELSE
        CLOSE curPDALimits;
    END IF;
EXCEPTION
    WHEN eExit THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecutePDA->Exited prematurely');
    WHEN eAppError THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, SQLERRM);
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR (-20005, 'YieldHandler.ExecutePDA->' || SQLERRM);
END ExecutePDA;

--===========================================================================
-- End of package body
--===========================================================================
END;
/
